import pymysql

class DBHelper:
    # 方法
    # 连接数据库
    def openDB(self):
        global conn
        conn = pymysql.connect(host='127.0.0.1', user='root', password='mysql123456', db='test', charset='utf8')
        global evo
        evo = conn.cursor()

    # 关闭数据库
    def closeDB(self):
        evo.close()
        conn.close()

    # 执行SQL语句
    def editDB(self, sql):
        self.openDB()
        result = evo.execute(sql)
        conn.commit()
        self.closeDB()
        return result
    
    # 查询一条
    def queryOne(self, sql):
        self.openDB()
        evo.execute(sql)
        result = evo.fetchone()
        self.closeDB()
        return result
    
    # 查询全部
    def queryAll(self, sql):
        self.openDB()
        evo.execute(sql)
        result = evo.fetchall()
        self.closeDB()
        return result
        
# 子类
class BookDAL(DBHelper):
    # 增加图书
    def addBook(self, book):
        sql = f"insert into book values({book.bid}, '{book.title}', '{book.author}', '{book.publisher}', '{book.price}', now())"
        return self.editDB(sql)
        
    # 根据id删除图书
    def delBook(self, id):
        sql = f"delete from book where bid={id}"
        return self.editDB(sql)
        
    # 修改图书
    def updateBook(self, book):
        sql = f"update book set title='{book.title}', author='{book.author}', publisher='{book.publisher}', price={book.price} where bid={book.bid}"
        return self.editDB(sql)
    
    # 根据id查询图书
    def selectBookById(self, id):
        sql = f"select * from book where bid={id}"
        return self.queryOne(sql)
    
    # 查询所有图书
    def selectAllBook(self):
        sql = "select * from book"
        return self.queryAll(sql)
    
        

        
